package cn.edu.nks.dormitory.admin.stumanage.dbimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import cn.edu.nks.dormitory.admin.stumanage.db.IDBService;
import cn.edu.nks.dormitory.model.StudentInfo;
import cn.edu.nks.util.DBConnectProvider;
import cn.edu.nks.util.DateTimeUtil;

public class DBService implements IDBService{
	private static Logger logger = Logger.getLogger(DBService.class);
	
	public int getCount() {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int count = 0;
		
		con = DBConnectProvider.getConnection();
		if(null == con){
			logger.warn("get connect fail");
			return 0;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) as count from studentinfo");
		try {
			stmt = con.prepareStatement(sql.toString());
			rs = stmt.executeQuery();
			if(rs.next()){
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			logger.warn(String.format("getCount is error", e));
		}
		DBConnectProvider.freeConnection(con);
		return count;
	}

	public List<StudentInfo> getStudents(int page, int size) {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<StudentInfo> items = new ArrayList<StudentInfo>();
		
		con = DBConnectProvider.getConnection();
		if(null == con){
			logger.warn("get connect fail");
			return null;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("select * from studentinfo limit ?,?");
		try {
			stmt = con.prepareStatement(sql.toString());
			stmt.setInt(1, (page-1)*size);
			stmt.setInt(2, size);
			rs = stmt.executeQuery();
			while(rs.next()){
				StudentInfo item = new StudentInfo();
				item.setAddr(rs.getString("addr"));
				item.setBirthday(DateTimeUtil.dateNow());
				item.setClassId(rs.getInt("classId"));
				item.setGrade(rs.getInt("grade"));
				item.setEmail(rs.getString("email"));
				item.setHeader(rs.getString("header"));
				item.setHeight(rs.getInt("height"));
				item.setIncomedate(new Date(rs.getTimestamp("incomedate").getTime()));
				item.setJsons(rs.getString("jsons"));
				item.setRealName(rs.getString("realname"));
				item.setRegion(rs.getString("region"));
				item.setSex(rs.getString("sex"));
				item.setStudentCode(rs.getString("studentcode"));
				item.setStudentId(rs.getInt("studentid"));
				item.setTelephone(rs.getString("telephone"));
				item.setTime(new Date(rs.getTimestamp("time").getTime()));
				item.setUserId(rs.getInt("userid"));
				item.setWeight(rs.getDouble("weight"));
				item.setJsons(rs.getString("jsons"));
				item.setBirthday(new Date(rs.getTimestamp("birthday").getTime()));
				items.add(item);
			}
		} catch (SQLException e) {
			logger.warn(String.format("getStudents is error", e));
		}
		DBConnectProvider.freeConnection(con);
		return items;
	}

}
